package com.parkreport.admin.city.service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import com.common.base.BaseService;
import com.common.jdbc.DB4ObjUtil;
import com.common.jdbc.DBBaseUtil;
import com.common.jdbc.DBSource;
import com.common.pager.PageHelper;
import com.common.util.StringUtil;
import com.parkreport.vo.CityVO;

public class CityService extends BaseService {

	public List find(CityVO vo,PageHelper pager){
		StringBuilder sql = new StringBuilder("select c.*,lc.lc.LAREA_ID from CITY c left join LAREA_CITY lc on c.ID=lc.CITY_ID where 1=1 ");
		if(vo!=null){
			if(!StringUtil.isBlank(vo.getCityName())){
				sql.append(" and CITY_NAME like '%"+vo.getCityName()+"%'");
			}
		}
		
		List<CityVO> list = DB4ObjUtil.executePager4Obj(new CityVO(), sql.toString(),pager);
		
		return list;
	}
	public List find(){
		StringBuilder sql = new StringBuilder("select c.*,lc.lc.LAREA_ID from CITY c left join LAREA_CITY lc on c.ID=lc.CITY_ID where 1=1 ");
		
		
		List<CityVO> list = DB4ObjUtil.execute4Obj(new CityVO(), sql.toString());
		
		return list;
	}
	
	public CityVO get(String id){
		String sql = "select * from CITY where 1=1 and ID=?";
		List<CityVO> list = DB4ObjUtil.execute4Obj(new CityVO(), sql, id);
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}
	

	public void delete(String ids){
		String sql = "delete from CITY where ID=?";
		
		if(!StringUtil.isBlank(ids)){
			String[] idArr = ids.split(",");
			DBBaseUtil.executeBatchUpdate(sql, idArr);
			
		}
	}
	
	public void delete(String[] ids){
		String sql = "delete from CITY where ID=?";
		
		if(!StringUtil.isBlank(ids)){
			DBBaseUtil.executeBatchUpdate(sql, ids);
			
		}
		
		
	}
	public String save(CityVO vo){
		if(vo!=null){
			String isHave = isApply(vo);
			if("0".equals(isHave)){
				if(StringUtil.isBlank(vo.getId())){
						String id = StringUtil.getUuid();
						String sql = "insert into CITY(ID,CITY_NAME) values(?,?)";
						DBBaseUtil.executeUpdate(sql,id, vo.getCityName());
				}else{
					
					String sql = "update CITY set CITY_NAME=?  where ID=?";
					DBBaseUtil.executeUpdate(sql, vo.getCityName(),vo.getId());
				}
			}else{
				return "0";
			}
		}
		return "1";
	}
	public String saveList(List<CityVO> cityList){
		Connection conn = DBSource.getConnection();
		try {
		conn.setAutoCommit(false);
		String delSql = "delete from LAREA_CITY";
		String insertSql = "insert into LAREA_CITY(LAREA_ID,CITY_ID) values(?,?)";
		DBBaseUtil.executeUpdateByConn(conn, delSql);
		for (int i = 0; i < cityList.size(); i++) {
			CityVO c = cityList.get(i);
			DBBaseUtil.executeUpdateByConn(conn, insertSql, c.getLareaId(),c.getId());
		}
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return "1";
	}
	public String isApply(String carNum,String id){
		String sql = "select * from CITY where 1=1 and CITY_NAME=? and ID<>?";
		List<CityVO> list = DB4ObjUtil.execute4Obj(new CityVO(), sql, carNum,id);
		if(list.size()>0){
			return "1";
		}
		return "0";
	}
	
	public String isApply(CityVO vo){
		String sql = "select * from CITY where 1=1 and CITY_NAME=? and ID<>?";
		List<CityVO> list = DB4ObjUtil.execute4Obj(new CityVO(), sql, vo.getCityName(),vo.getId());
		if(list.size()>0){
			return "1";
		}
		return "0";
	}
}
